﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using ThreeByte.ActiveDeck.Service;
using System.Transactions;
using log4net;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace ThreeByte.ActiveDeck.Data
{
    public enum UserAccess { Anonymous = 0, Registered = 1, Approved = 2 };

    public static class DataUtil
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(DataUtil));

        private static readonly XNamespace ns = ServiceConfiguration.NAMESPACE;

        public static ActiveDeckDataContext GetDataContext() {

            //string connString = @"Data Source=localhost\SQLEXPRESS;Initial Catalog=ActiveDeck;User ID=ActiveDeck;Password=ad7907";
            string connString = @"Data Source=gqs1yt87cy.database.windows.net;Initial Catalog=ActiveDeck;User ID=ActiveDeckSQLAccount;Password=765^^^happyandSAD";
            ActiveDeckDataContext db = new ActiveDeckDataContext(connString);
            return db;
        }

        public static string GetConfigValue(string param) {
            ActiveDeckDataContext db = GetDataContext();

            db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, db.Configs);
            try {
                string value = (from c in db.Configs
                                where c.Parameter == param
                                select c.Value).Single();
                return value;
            } catch(Exception ex) {
                log.Error("Error getting config value from the database: " + param, ex);
                throw;
            }
        }

        public static bool SetConfigValue(string param, string value) {
            ActiveDeckDataContext db = GetDataContext();

            try {
                Config config = (from c in db.Configs
                                where c.Parameter == param
                                select c).SingleOrDefault();

                bool isNew = false;
                if(config == null) {
                    isNew = true;
                    config = new Config();
                    config.Parameter = param;
                    db.Configs.InsertOnSubmit(config);
                }
                config.Value = value;

                db.SubmitChanges();

                return isNew;
            } catch(Exception ex) {
                log.Error("Error setting config value in database: " + param, ex);
                throw;
            }
        }


        public static Presentation CreateNewPresentation(string name) {
            
            ActiveDeckDataContext db = DataUtil.GetDataContext();

            Presentation newPresentation = new Presentation();
            newPresentation.Name = name;
            newPresentation.Created = DateTime.Now;
            //Create new Presentation ID
            newPresentation.ID = GenerateNewPresentationID();

            db.Presentations.InsertOnSubmit(newPresentation);
            db.SubmitChanges();
            db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, newPresentation);

            return newPresentation;
        }

        public static void AddPresenter(Presentation pres, string username) {

            ActiveDeckDataContext db = DataUtil.GetDataContext();

            Presenter presenter = (from p in db.Presenters
                                   where p.PresentationID == pres.ID
                                     && p.UserName == username
                                   select p).SingleOrDefault();
            if(presenter != null) {
                return;
            }

            Presenter newPresenter = new Presenter();
            newPresenter.PresentationID = pres.ID;
            newPresenter.UserName = username;

            db.Presenters.InsertOnSubmit(newPresenter);
            db.SubmitChanges();

            return;
        }

         public static Presentation RenamePresentation(string presentation, string name) {
            //Find the current presentation

             ActiveDeckDataContext db = DataUtil.GetDataContext();

             Presentation pres = (from p in db.Presentations
                                 where p.ID == presentation
                                 select p).Single();
             pres.Name = name;
             db.SubmitChanges();
             db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, pres);

             return pres;
        }

         public static PresentationHeader RecordPresentationStart(string presentation) {
             ActiveDeckDataContext db = DataUtil.GetDataContext();

             Presentation pres = (from p in db.Presentations
                                  where p.ID == presentation
                                  select p).Single();

             pres.PresentationCount += 1;
             pres.LastPresented = DateTime.Now;
             db.SubmitChanges();
             db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, pres);

             return new PresentationHeader(pres);
         }


        //public static XElement ToXML(Presentation pres) {
        //    XElement presNode = new XElement(ns + "Presentation");
        //    presNode.Add(new XAttribute("ID", pres.ID));
        //    presNode.Add(new XAttribute("Name", pres.Name));
        //    presNode.Add(new XAttribute("Created", pres.Created));
        //    presNode.Add(new XAttribute("LastModified", pres.LastModified.ToString()));
        //    presNode.Add(new XAttribute("LastPresented", pres.LastPresented.ToString()));
        //    presNode.Add(new XAttribute("Version", pres.Version));
        //    presNode.Add(new XAttribute("PresentationCount", pres.PresentationCount));
        //    return presNode;
        //}

        //****************************************************
        //PRIVATE METHODS
        //****************************************************
        private static readonly int A_PRIME_NUMBER = 7907;
        private static readonly int MIN_ID = 100000;
        private static readonly int MAX_ID = 1000000;

        private static string GenerateNewPresentationID() {
            string currentMax = GetMaxPresentationID();
            int presentationID = MIN_ID;
            int.TryParse(currentMax, out presentationID);
            presentationID -= MIN_ID; //Normalize: [0, 900000)
            presentationID = Math.Max(0, presentationID);

            string presentationIDString = string.Format("{0:000000}", presentationID + MIN_ID);
            while (PresentationIDExists(presentationIDString)) {
                presentationID += A_PRIME_NUMBER;
                presentationID = (presentationID % (MAX_ID - MIN_ID));
                presentationIDString = string.Format("{0:000000}", presentationID + MIN_ID);
            }
            return presentationIDString;
        }


        private static bool PresentationIDExists(string id) {
            ActiveDeckDataContext db = GetDataContext();
            var pres = (from p in db.Presentations
                        where p.ID == id
                        select p).FirstOrDefault();
            return (pres != null);
        }

        private static string GetMaxPresentationID() {
            ActiveDeckDataContext db = GetDataContext();
            string pres = (from p in db.Presentations
                        select p.ID).Max();
            return pres;
        }

        public static readonly UserAccess DEFAULT_USERACCESS = UserAccess.Registered;

        public static Presentation UpdatePresentationConfig(string presentationId, XElement presentationData) {
            ActiveDeckDataContext db = DataUtil.GetDataContext();

            using(TransactionScope ts = new TransactionScope()) {

                Presentation pres = (from p in db.Presentations
                                     where p.ID == presentationId
                                     select p).Single();
                //Increment version
                int version = pres.Version + 1;

                PresentationConfig newConfig = new PresentationConfig();
                newConfig.PresentationID = presentationId;
                newConfig.Version = version;
                //Strip Authentication Information
                presentationData.Element(ns + "Authentication").Remove();

                //Insert the version attribute (in a nice place in the attribute list
                List<XAttribute> currAttributes = presentationData.Attributes().ToList();
                currAttributes.Insert(1, new XAttribute("Version", version));  //Careful of this magic number 1
                presentationData.ReplaceAttributes(currAttributes);
                newConfig.XML = presentationData.ToString();

                UserAccess userAccess = DEFAULT_USERACCESS;

                //Retrieve UserAccess Element
                XElement userAccessNode = presentationData.Element(ns + "UserAccess");
                if(userAccessNode != null) {
                    userAccess = DataUtil.StringToUserAccess(userAccessNode.Attribute("Value").Value);
                }

                db.PresentationConfigs.InsertOnSubmit(newConfig);
                pres.Version = version;
                pres.LastModified = DateTime.Now;
                pres.UserAccess = (int)userAccess;

                db.SubmitChanges();  //Commit changes transactionally

                ts.Complete();

                //db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, pres);
                return pres;
            }
        }

        public static XElement GetPresentationInfo(string presentation) {

            ActiveDeckDataContext db = DataUtil.GetDataContext();

            Presentation pres = (from p in db.Presentations
                                 where p.ID == presentation
                                 select p).Single();
            XElement presInfo = (new PresentationHeader(pres)).ToXml();

            XElement userAccessNode = new XElement(ns + "UserAccess");
            UserAccess userAccess = (UserAccess)(pres.UserAccess);
         
            userAccessNode.Add(new XAttribute("Value", UserAccessToString(userAccess)));
            presInfo.Add(userAccessNode);

            return presInfo;
        }

        public static XElement GetPresentationConfig(string presentation, int version = 0) {
            return GetPresentationConfig(presentation, ref version);
        }

        public static XElement GetPresentationConfig(string presentation, ref int version) {

            ActiveDeckDataContext db = DataUtil.GetDataContext();

            if(version == 0) {
                PresentationConfig pres = (from p in db.PresentationConfigs
                                           where p.PresentationID == presentation
                                           orderby p.Version descending
                                           select p).First();
                version = pres.Version;
                return XElement.Parse(pres.XML);
            } else {
                int versionValue = version;
                PresentationConfig pres = (from p in db.PresentationConfigs
                                           where p.PresentationID == presentation
                                           && p.Version == versionValue
                                           select p).Single();

                return XElement.Parse(pres.XML);
            }

        }

        public static string UserAccessToString(UserAccess userAccess) {
            switch(userAccess) {
                case UserAccess.Anonymous:
                    return "Anonymous";
                case UserAccess.Registered:
                    return "Registered";
                case UserAccess.Approved:
                    return "Approved";
            }
            throw new ArgumentOutOfRangeException("The argument could not be identified", "userAccess");
        }

        public static UserAccess StringToUserAccess(string userAccess) {
            switch(userAccess) {
                case "Anonymous":
                    return UserAccess.Anonymous;
                case "Registered":
                    return UserAccess.Registered;
                case "Approved":
                    return UserAccess.Approved;
            }
            throw new ArgumentOutOfRangeException("The argument could not be identified", "userAccess");
        }

        public static Presentation GetPresentation(string presentation) {
            ActiveDeckDataContext db = DataUtil.GetDataContext();

            Presentation pres = (from p in db.Presentations
                                 where p.ID == presentation
                                 select p).Single();
            return pres;
        }

        public static void SetPresentationState(string presentation, int section, string channel, int updateId) {
            ActiveDeckDataContext db = DataUtil.GetDataContext();

            PresentationState state = (from s in db.PresentationStates
                                       where s.PresentationID == presentation
                                       select s).SingleOrDefault();

            if(state == null) {
                state = new PresentationState();
                state.PresentationID = presentation;
                db.PresentationStates.InsertOnSubmit(state);
            }

            XElement newPresentationState = new XElement(ns + "Presentation", new XAttribute("Presentation", presentation), new XAttribute("Section", section));
            if(channel != null) {
                newPresentationState.Add(new XAttribute("Channel", channel));
            }
            if(updateId != 0) {
                newPresentationState.Add(new XAttribute("MessageID", updateId));
            }
            state.SectionData = newPresentationState.ToString();

            try {
                db.SubmitChanges();
            } catch(ChangeConflictException ex) {
                log.Warn("Set Presentation State Change Conflict", ex);
                foreach(ObjectChangeConflict occ in db.ChangeConflicts) {
                    occ.Resolve(RefreshMode.KeepCurrentValues);
                }
            }

        }


        public static XElement GetPresentationState(string presentation) {
            ActiveDeckDataContext db = DataUtil.GetDataContext();

            PresentationState state = (from s in db.PresentationStates
                                       where s.PresentationID == presentation
                                       select s).SingleOrDefault();

            if(state == null) {
                //If the PresentationState does not exist, look to see if the presentation exists
                Presentation pres = (from p in db.Presentations
                                     where p.ID == presentation
                                     select p).Single();
                //If it doesn't exist, this will throw
                //If it does, send a default Presentation state
                return new XElement(ns + "Presentation", new XAttribute("Presentation", presentation), new XAttribute("Section", 0));
            }

           return XElement.Parse(state.SectionData);
        }

        public static Guid RecordDownloadRequest(string emailAddress) {

            DownloadRequest req = new DownloadRequest();
            req.ID = Guid.NewGuid();
            req.EmailAddress = emailAddress;
            req.Timestamp = DateTime.Now;

            ActiveDeckDataContext db = GetDataContext();
            db.DownloadRequests.InsertOnSubmit(req);
            db.SubmitChanges();

            return req.ID;
        }

        public static bool IsDownloadKeyValid(Guid key) {

            try {
                ActiveDeckDataContext db = GetDataContext();

                DownloadRequest req = (from d in db.DownloadRequests
                                      where d.ID == key
                                      select d).Single();
                if(req.Timestamp > (DateTime.Now - TimeSpan.FromDays(1))) {
                    return true;
                }
                return false;
            } catch(Exception ex) {
                //Log this
                log.Warn("DownloadKey error", ex);
                return false;
            }
        }

        public static string GetEmailForDownloadKey(Guid key) {

            try {
                ActiveDeckDataContext db = GetDataContext();

                return (from d in db.DownloadRequests
                                       where d.ID == key
                                       select d.EmailAddress).Single();
                
            } catch(Exception ex) {
                log.Warn("GetEmailForDownloadKey error", ex);
                return null;
            }

        }


        public static void RecordDownload(string username, string version) {

            try {
                ActiveDeckDataContext db = GetDataContext();
                AddInDownload dl = new AddInDownload();

                dl.UserName = username;
                dl.Version = version;
                dl.Timestamp = DateTime.Now;

                db.AddInDownloads.InsertOnSubmit(dl);
                db.SubmitChanges();
            } catch(Exception ex) {
                log.Error("Cannot record AddIn download", ex);
            }
        }


        public static Guid RecordEmailChallenge(string emailAddress) {
            //Delete any existing records with this email
            ActiveDeckDataContext db = GetDataContext();

            var toDelete = from e in db.EmailVerifications
                           where e.Email == emailAddress
                           select e;
            db.EmailVerifications.DeleteAllOnSubmit(toDelete);
            db.SubmitChanges();

            EmailVerification req = new EmailVerification();
            req.ID = Guid.NewGuid();
            req.Email = emailAddress;
            req.ChallengedAt = DateTime.Now;
            
            db.EmailVerifications.InsertOnSubmit(req);
            db.SubmitChanges();

            return req.ID;
        }

        public static bool IsEmailChallengeValid(Guid key) {

            try {
                ActiveDeckDataContext db = GetDataContext();

                EmailVerification req = (from d in db.EmailVerifications
                                         where d.ID == key
                                         select d).Single();
                if(req.ChallengedAt > (DateTime.Now - TimeSpan.FromDays(1))) {
                    return true;
                }
                return false;
            } catch(Exception ex) {
                //Log this
                log.Warn("Email Verification Key error", ex);
                return false;
            }
        }

        public static void MarkEmailAsVerified(Guid key) {

            ActiveDeckDataContext db = GetDataContext();

            EmailVerification req = (from d in db.EmailVerifications
                                     where d.ID == key
                                     select d).Single();
            req.Verified = true;
            req.VerifiedAt = DateTime.Now;

            db.SubmitChanges();
        }

        public static bool IsEmailVerified(string email) {

            ActiveDeckDataContext db = GetDataContext();

            return (from e in db.EmailVerifications
                     where e.Email == email
                     && e.Verified == true
                     select e).SingleOrDefault() != null;
        }


        public static Guid RequestPasswordReset(string email) {

            //Create new db record
            PasswordResetRequest newRequest = new PasswordResetRequest();
            newRequest.ID = Guid.NewGuid();
            newRequest.Email = email;
            newRequest.RequestedAt = DateTime.Now;

            ActiveDeckDataContext db = GetDataContext();
            db.PasswordResetRequests.InsertOnSubmit(newRequest);
            db.SubmitChanges();

            return newRequest.ID;
        }

        public static bool IsPasswordResetRequestValid(Guid key) {
            //See if this key exists and has not been used
            try {
                ActiveDeckDataContext db = GetDataContext();

                PasswordResetRequest req = (from d in db.PasswordResetRequests
                                         where d.ID == key && (d.Reset == false)
                                         select d).Single();
                if(req.RequestedAt > (DateTime.Now - TimeSpan.FromDays(1))) {
                    return true;
                }
                return false;
            } catch(Exception ex) {
                //Log this
                log.Warn("Password Reset Key error", ex);
                return false;
            }
        }

        public static void RecordPasswordReset(Guid key) {
            //Update the appropriate record
            ActiveDeckDataContext db = GetDataContext();

            PasswordResetRequest req = (from d in db.PasswordResetRequests
                                     where d.ID == key
                                     select d).Single();
            req.Reset = true;
            req.ResetAt = DateTime.Now;

            db.SubmitChanges();
        }

        public static string GetEmailForPasswordResetKey(Guid key) {

            try {
                ActiveDeckDataContext db = GetDataContext();
                return (from d in db.PasswordResetRequests
                        where d.ID == key
                        select d.Email).Single();
            } catch(Exception ex) {
                log.Warn("GetEmailForPasswordResetKey error", ex);
                return null;
            }

        }


        public static Guid RecordErrorReport(string source, string version, string presentationId, string udid) {

            ActiveDeckDataContext db = GetDataContext();

            ErrorReport newReport = new ErrorReport();
            newReport.ID = Guid.NewGuid();
            newReport.Timestamp = DateTime.Now;
            newReport.Source = source;
            newReport.Version = version;
            newReport.PresentationID = presentationId;
            newReport.UDID = udid;

            db.ErrorReports.InsertOnSubmit(newReport);

            db.SubmitChanges();

            return newReport.ID;
        }

        public static void SetUserGroup(string presentation, string username, int groupID) {
            ActiveDeckDataContext db = GetDataContext();

            UserGroup userGroup = (from u in db.UserGroups
                                   where u.PresentationID == presentation
                                   && u.User == username
                                   select u).SingleOrDefault();
            
            if (null == userGroup) {
                userGroup = new UserGroup();
                userGroup.PresentationID = presentation;
                userGroup.User = username;

                db.UserGroups.InsertOnSubmit(userGroup);
            }

            userGroup.GroupID = groupID;

            db.SubmitChanges();
        }

        public static XElement GetGroupUsers(string presentation, int groupID) {
            ActiveDeckDataContext db = GetDataContext();

            var users = (from ug in db.UserGroups
                         where ug.GroupID == groupID && ug.PresentationID == presentation
                         select new { ug.User });

            XElement groupUsers = new XElement(ns + "GroupUsers");
            groupUsers.Add(new XAttribute("ID", groupID), new XAttribute("NumOfUsers", users.Count()));
            if (users.Count() > 0) {                
                foreach (var u in users) {
                    XElement userNode = new XElement(ns + "User");
                    userNode.Add(new XAttribute("UserName", u.User), new XAttribute("FirstName", ""), new XAttribute("LastName", ""));
                    groupUsers.Add(userNode);
                }
            }
            return groupUsers;
        }

        public static void RemoveUserFromGroup(string presentation, string username, int groupID) {
            ActiveDeckDataContext db = GetDataContext();

            var users = from ug in db.UserGroups
                         where ug.GroupID == groupID && ug.PresentationID == presentation
                         && ug.User == username
                         select ug;

            foreach(var user in users) {
                db.UserGroups.DeleteOnSubmit(user);
            }

            try {
                db.SubmitChanges();
            } catch(Exception ex) {
                log.Error(string.Format("Error removing user {0} from groupID {1}.", username, groupID), ex);            
            }
        }

        public static void RemovePresentationGroup(string presentation, int groupID) {
            ActiveDeckDataContext db = GetDataContext();

            var groups = from ug in db.UserGroups
                         where ug.GroupID == groupID && ug.PresentationID == presentation
                         select ug;

            foreach(var group in groups) {
                db.UserGroups.DeleteOnSubmit(group);
            }

            try {
                db.SubmitChanges();
            } catch(Exception ex) {
                log.Error(string.Format("Error removing group {0}", groupID), ex);
            }
        }
    }    
}
